package com.schoolgard.base;

import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.FlushMode;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.schoolgard.base.query.ComparatorForSearchModel;
import com.schoolgard.base.query.SearchAttribute;
import com.schoolgard.base.query.SearchModel;
import com.supersystem.utils.Page;
import com.supersystem.utils.PageQueryParameter;


public class BaseDAOImpl<T> extends HibernateDaoSupport implements BaseDAO<T> {

    // 实体类类型(由构造方法自动赋值)
    public Class<T> entityClass;
    private String className;

    // 构造方法，根据实例类自动获取实体类类型
    public BaseDAOImpl() {
        this.entityClass = null;
        Class<?> c = getClass();
        Type t = c.getGenericSuperclass();
        if (t instanceof ParameterizedType) {
            Type[] p = ((ParameterizedType) t).getActualTypeArguments();
            this.entityClass = (Class<T>) p[0];
            this.className = entityClass.getSimpleName();
        }
    }

    public String getClassName() {
        return this.className;
    }

    protected Class<T> getPersitanceClass() {
        return this.entityClass;
    }

    protected String getClassAlias() {
        // return this.className.substring(0, 1).toLowerCase().concat(this.className.length()>1?
        // this.className.substring(1):"");
        return "instance";
    }

    // -------------------- 基本检索、增加、修改、删除操作 --------------------
    public T create(T instance) {
        logger.debug("persisting " + getClassName() + " instance");
        getHibernateTemplate().persist(instance);
        logger.debug("persist successful");
        return instance;
    }

    public void remove(T instance) {
        logger.debug("removing " + getClassName() + " instance");
        getHibernateTemplate().delete(instance);
        logger.debug("remove successful");
    }

    public void remove(Serializable id) {
        logger.debug("removing " + getClassName() + " instance");
        T instance = (T) getHibernateTemplate().get(getPersitanceClass(), id);
        getHibernateTemplate().delete(instance);
        logger.debug("remove successful");
    }

    public void remove(List<T> list) {
        getHibernateTemplate().deleteAll(list);
        logger.debug("remove all successful");
    }

    /**
     * 根据hql语句批量删除
     * 
     * @param hql
     */
    public void remove(final String hql) {
        this.getHibernateTemplate().execute(new HibernateCallback() {

            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(hql);
                return query.executeUpdate();
            }
        });
    }

    public T createOrModify(T instance) {
        getHibernateTemplate().saveOrUpdate(instance);// persist();
        return instance;
    }

    public void modify(T instance, boolean isMerge) {
        logger.debug("modifing " + getClassName() + " instance");
        isMerge = isMerge ? isMerge : !getHibernateTemplate().contains(instance);
        if (isMerge) {
            instance = (T) getHibernateTemplate().merge(instance);
        } else {
            getHibernateTemplate().persist(instance);
        }
        logger.debug("modify successful");
    }

    public void modify(T instance) {
        modify(instance, false);
    }

    public void clear(T instance) {
        getSessionFactory().getCurrentSession().evict(instance);
        getSessionFactory().evict(instance.getClass());
    }

    public T findById(Serializable id) {
        if (logger.isDebugEnabled()) {
            logger.debug("finding " + getPersitanceClass().getSimpleName() + " instance with id: " + id);
        }
        T instance = (T) getHibernateTemplate().get(getPersitanceClass(), id);
        if (logger.isDebugEnabled()) {
            logger.debug("find instance " + instance);
        }
        return instance;
    }

    public int getTotalCount() {
        // Session session = getSession(true);
        // Query q = session.createQuery("SELECT count(*) FROM " + getClassName());
        // try {
        // Object result = q.uniqueResult();
        // if (result == null)
        // return 0;
        // return (Long) result;
        // } catch (Exception e) {
        // return 0;
        // } finally {
        // session.close();
        // }
        String hql = "SELECT count(*) FROM " + getClassName();
        List<?> list = getHibernateTemplate().find(hql);
        int count = Integer.valueOf(list.get(0).toString());
        return count;
    }

    // -------------------- HQL检索 --------------------

    public List<T> find(String hql) {
        logger.debug("finding the " + getClassName() + " instances by hql");
        return (List<T>) getHibernateTemplate().find(hql);
    }

    public List<T> find(String hql, Object paramater) {
        return (List<T>) getHibernateTemplate().find(hql, paramater);
    }

    public List<T> find(String hql, Object[] paramaters) {
        return (List<T>) getHibernateTemplate().find(hql, paramaters);
    }

    public List<T> find(final String hql, final int firstResult, final int maxResult) {
        logger.debug("finding the " + getClassName() + " instances by hql");
        List<T> results = (List<T>) getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = session.createQuery(hql);
                q.setFirstResult(firstResult);
                q.setMaxResults(maxResult);
                return q.list();
            }
        });
        return results;
    }

    public List<T> find(final String hql, final Object[] paramaters, final int firstResult, final int maxResult) {
        logger.debug("finding the " + getClassName() + " instances by hql");
        List<T> results = (List<T>) getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = session.createQuery(hql);
                for (int i = 0; i < paramaters.length; i++) {
                    q.setParameter(i, paramaters[i]);
                }
                q.setFirstResult(firstResult);
                q.setMaxResults(maxResult);
                return q.list();
            }
        });
        return results;
    }

    public List<?> findResults(final String hql, final Object[] paramaters, final int firstResult, final int maxResult) {
        logger.debug("finding the " + getClassName() + " instances by hql");
        List<?> results = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = session.createQuery(hql);
                for (int i = 0; i < paramaters.length; i++) {
                    q.setParameter(i, paramaters[i]);
                }
                q.setFirstResult(firstResult);
                q.setMaxResults(maxResult);
                return q.list();
            }
        });
        return results;
    }

    public List<T> find(String hql, Object paramater, int firstResult, int maxResult) {
        Object[] paramaters = {paramater};
        return find(hql, paramaters, firstResult, maxResult);
    }

    public List<T> findAll() {
        String hql = "FROM " + getClassName();
        return this.find(hql);
    }

    public List<T> findAll(int firstResult, int maxResult) {
        String hql = "FROM " + getClassName();
        return this.find(hql, firstResult, maxResult);
    }

    public List<T> query(String filter) {
        String hql = "FROM " + getClassName() + " as " + getClassAlias() + " where 1=1 " + filter;
        return this.find(hql);
    }

    public List<T> query(String filter, int firstResult, int maxResult) {
        String hql = "FROM " + getClassName() + " as " + getClassAlias() + " where 1=1 " + filter;
        return this.find(hql, firstResult, maxResult);
    }

    public List<T> query(String filter, Object[] params) {
        String hql = "FROM " + getClassName() + " as " + getClassAlias() + " where 1=1 " + filter;
        return this.find(hql, params);
    }

    public List<T> query(String filter, Object[] params, int firstResult, int maxResult) {
        String hql = "FROM " + getClassName() + " as " + getClassAlias() + " where 1=1 " + filter;
        return this.find(hql, params, firstResult, maxResult);
    }

    public List<T> findByExample(T example) {
        logger.debug("finding the " + getClassName() + " instances by exampleEntity");
        List<T> results = (List<T>) getHibernateTemplate().findByExample(example);
        return results;
    }

    public List<T> findByProperty(String propertyName, Object value) {
        String queryString = "from " + getClassName() + " as " + getClassAlias() + " where " + getClassAlias() + "."
                             + propertyName + "= ? ";
        return this.find(queryString, value);
    }

    public List<T> findByProperty(String propertyName, Object value, int firstResult, int maxResult) {
        String queryString = "from " + getClassName() + " as " + getClassAlias() + " where " + getClassAlias() + "."
                             + propertyName + "= ? ";
        return this.find(queryString, value, firstResult, maxResult);
    }

    public List<T> findByProperty(String[] propertyNames, Object[] values) {
        String queryString = "from " + getClassName() + " as " + getClassAlias() + " where";
        for (String propertyName : propertyNames) {
            queryString += " " + getClassAlias() + "." + propertyName + "=? and";
        }
        queryString = StringUtils.removeEnd(queryString, "and");
        // queryString=addOrderBy(queryString);
        return this.find(queryString, values);
    }

    public List<T> findByProperty(String[] propertyNames, Object[] values, int firstResult, int maxResult) {
        String queryString = "from " + getClassName() + " as " + getClassAlias() + " where";
        for (String propertyName : propertyNames) {
            queryString += " " + getClassAlias() + "." + propertyName + "=? and";
        }
        queryString = StringUtils.removeEnd(queryString, "and");
        // queryString=addOrderBy(queryString);
        return this.find(queryString, values, firstResult, maxResult);
    }

    public List<T> fuzzyFindByProperty(String propertyName, Object value) {
        String queryString = " from " + getClassName() + " as " + getClassAlias() + " where LOWER(" + getClassAlias()
                             + "." + propertyName + ") like ?";
        String lowerValue = "%" + String.valueOf(value).toLowerCase() + "%";
        // queryString = addOrderBy(queryString);
        return this.find(queryString, lowerValue);
    }

    public List<T> fuzzyFindByProperty(String propertyName, Object value, int firstResult, int maxResult) {
        String queryString = " from " + getClassName() + " as " + getClassAlias() + " where LOWER(" + getClassAlias()
                             + "." + propertyName + ") like ?";
        String lowerValue = "%" + String.valueOf(value).toLowerCase() + "%";
        // queryString = addOrderBy(queryString);
        return this.find(queryString, lowerValue, firstResult, maxResult);
    }

    public List<T> fuzzyFindByProperty(String[] propertyNames, Object[] values) {
        String queryString = " from " + getClassName() + " as " + getClassAlias() + " where ";
        for (String propertyName : propertyNames) {
            queryString += " LOWER(" + getClassAlias() + "." + propertyName + ") like ? and";
        }
        queryString = StringUtils.removeEnd(queryString, "and");
        Object[] fuzzyValues = new Object[values.length];
        for (int i = 0; i < values.length; i++) {
            fuzzyValues[i] = "%" + values[i].toString().toLowerCase() + "%";
        }
        // queryString = addOrderBy(queryString);
        return this.find(queryString, fuzzyValues);
    }

    public List<T> fuzzyFindByProperty(String[] propertyNames, Object[] values, int firstResult, int maxResult) {
        String queryString = " from " + getClassName() + " as " + getClassAlias() + " where ";
        for (String propertyName : propertyNames) {
            queryString += " LOWER(" + getClassAlias() + "." + propertyName + ") like ? and";
        }
        queryString = StringUtils.removeEnd(queryString, "and");
        Object[] fuzzyValues = new Object[values.length];
        for (int i = 0; i < values.length; i++) {
            fuzzyValues[i] = "%" + values[i].toString().toLowerCase() + "%";
        }
        // queryString = addOrderBy(queryString);
        return this.find(queryString, fuzzyValues, firstResult, maxResult);
    }

    /**
     * hql方式查询实体并初始化(关联)指定子对象 hql语句所检索的主体的别名为该instance
     * 
     * @param properties 要初始化(关联)的子对象
     * @param alias 指定子对象的别名
     * @param filter 自行构造的hql子句，例如"where ...... order by ......"
     * @return @
     */
    public List<T> findJoinProperty(String[] properties, String[] alias, String filter) throws DataBaseAccessException {
        String hql = " from " + getClassName() + " as " + getClassAlias();
        if (alias.length != properties.length) {
            logger.error("the properties.length and alias.length must be equal!");
            throw new DataBaseAccessException("the properties.length and alias.length must be equal!");
        }
        for (int i = 0; i < properties.length; i++) {
            hql += " left join fetch " + getClassAlias() + "." + properties[i] + " as " + alias[i];
        }
        hql += " " + filter;
        return this.find(hql);
    }

    /**
     * hql方式查询实体并初始化(关联)指定子对象 hql语句所检索的主体的别名为该instance
     * 
     * @param properties 要初始化(关联)的子对象
     * @param alias 指定子对象的别名
     * @param filter 自行构造的hql子句，例如"where Member.number=? order by ......"
     * @param filterParams filter子句中的参数组
     * @return @
     */
    public List<T> findJoinProperty(String[] properties, String[] alias, String filter, Object[] filterParams)
            throws DataBaseAccessException {
        String hql = " from " + getClassName() + " as " + getClassAlias();
        if (alias.length != properties.length) {
            logger.error("the properties.length and alias.length must be equal!");
            throw new DataBaseAccessException("the properties.length and alias.length must be equal!");
        }
        for (int i = 0; i < properties.length; i++) {
            hql += " left join fetch " + getClassAlias() + "." + properties[i] + " as " + alias[i];
        }
        hql += " " + filter;
        return this.find(hql, filterParams);
    }

    /**
     * hql方式查询实体并初始化(关联)指定子对象 hql语句所检索的主体的别名为该instance hql中欲初始化的子对象的别名默认与参数properties[i]等值
     * 
     * @param properties 要初始化(关联)的子对象
     * @param filter 自行构造的hql子句，例如"where ...... order by ......"
     * @return @
     */
    public List<T> findJoinProperty(String[] properties, String filter) {
        String hql = " from " + getClassName() + " as " + getClassAlias();
        for (String property : properties) {
            hql += " left join fetch " + getClassAlias() + "." + property + " as " + property;
        }
        hql += " " + filter;
        return this.find(hql);
    }

    /**
     * hql方式查询实体并初始化(关联)指定子对象 hql语句所检索的主体的别名为该instance hql中欲初始化的子对象的别名默认与参数properties[i]等值
     * 
     * @param properties 要初始化(关联)的子对象
     * @param filter 自行构造的hql子句，例如"where Member.number=? order by ......"
     * @param filterParams filter子句中的参数组
     * @return @
     */
    public List<T> findJoinProperty(String[] properties, String filter, Object[] filterParams) {
        String hql = " from " + getClassName() + " as " + getClassAlias();
        for (String property : properties) {
            hql += " left join fetch " + getClassAlias() + "." + property + " as " + property;
        }
        hql += " " + filter;
        return this.find(hql, filterParams);
    }

    /**
     * hql方式查询实体并初始化(关联)指定子对象 hql语句所检索的主体的别名为该instance
     * 
     * @param properties 要初始化(关联)的子对象
     * @param alias 指定子对象的别名
     * @return @
     * @throws DataBaseAccessException
     */
    public List<T> findJoinProperty(String[] properties, String[] alias) throws DataBaseAccessException {
        return this.findJoinProperty(properties, alias, "");
    }

    /**
     * hql方式查询实体并初始化(关联)指定子对象 hql语句所检索的主体的别名为该instance hql中欲初始化的子对象的别名默认与参数properties[i]等值
     * 
     * @param properties 要初始化(关联)的子对象
     * @return @
     */
    public List<T> findJoinProperty(String[] properties) {
        return this.findJoinProperty(properties, "");
    }

    /**
     * 根据指定属性值查询实体并初始化(关联)指定子对象 (hql查询 eq查询)
     * 
     * @param properties 要初始化(关联)的子对象
     * @param propertyName 查询条件中的属性名称
     * @param eqValue 条件属性的匹配值
     * @return @
     */
    public T findJoinPropertyByProperty(String[] properties, String propertyName, Object eqValue) {
        String filter = "where " + getClassAlias() + "." + propertyName + "=?";
        Object[] filterParams = new Object[] {eqValue};
        List<T> list = this.findJoinProperty(properties, filter, filterParams);
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    // -------------------------------- Criteria 检索 ------------------------------
    public List<T> findByCriteria(DetachedCriteria criteria, int firstResult, int maxResults) {
        logger.debug("finding the " + getClassName() + " instances by criteria with page");
        return getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults);
    }

    public List<T> findByCriteria(DetachedCriteria criteria) {
    	if(logger.isDebugEnabled()){
    		logger.debug("finding the " + getClassName() + " instances by criteria");
    	}
        return getHibernateTemplate().findByCriteria(criteria);
    }

    public int getRowCount(DetachedCriteria criteria) {
    	if(logger.isDebugEnabled()){
    		logger.debug("get row count by criterial!");
    	}
        criteria.setProjection(Projections.rowCount());
        List<?> list = getHibernateTemplate().findByCriteria(criteria,0,1);
        return list.size() > 0 ? (Integer) list.get(0) : 0;
    }

    /**
     * 根据传入的List<SearchModel>进行QBC查询
     * 
     * @param searchModels 查询实体集
     * @param firstResult 首页
     * @param maxResults 总数
     * @return
     * @throws IllegalArgumentException
     * @throws SecurityException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
    public List<T> findBySearchModels(final List<SearchModel> searchModels, final int firstResult, final int maxResults)
            throws IllegalArgumentException, SecurityException, IllegalAccessException, InvocationTargetException,
            NoSuchMethodException {
        return getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Criteria criteria = session.createCriteria(entityClass);
                // 解析List<SearchModel>
                ComparatorForSearchModel comparator = new ComparatorForSearchModel();
                Collections.sort(searchModels, comparator);
                for (SearchModel searchModel : searchModels) {
                    if (!searchModel.getLevel().equals(0)) {
                        criteria = criteria.createCriteria(searchModel.getEntityProperty());
                    }
                    for (SearchAttribute searchattribute : searchModel.getSearchAttributes()) {
                        try {
                            criteria.add(HibernateHelper.invokeRestritionMethod(searchattribute.getConstraint(),
                                                                                searchattribute.getPropertyName(),
                                                                                searchattribute.getValue()));
                        } catch (Exception e) {
                            logger.error(e.getMessage(), e);
                        }
                    }
                }
                criteria.setFirstResult(firstResult);
                criteria.setMaxResults(maxResults);
                return criteria.list();
            }
        });
    }

    /**
     * 根据传入的List<SearchModel>条件查询记录总数
     * 
     * @param searchModels 查询实体集
     * @return
     * @throws IllegalArgumentException
     * @throws SecurityException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
    public int getRowCountBySearchModels(List<SearchModel> searchModels) throws IllegalArgumentException,
            SecurityException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        Criteria criteria = this.getSession(true).createCriteria(entityClass);
        // 解析List<SearchModel>
        ComparatorForSearchModel comparator = new ComparatorForSearchModel();
        Collections.sort(searchModels, comparator);
        for (SearchModel searchModel : searchModels) {
            if (!searchModel.getLevel().equals(0)) {
                criteria = criteria.createCriteria(searchModel.getEntityProperty());
            }
            for (SearchAttribute searchattribute : searchModel.getSearchAttributes()) {
                criteria.add(HibernateHelper.invokeRestritionMethod(searchattribute.getConstraint(),
                                                                    searchattribute.getPropertyName(),
                                                                    searchattribute.getValue()));
            }
        }
        criteria.setProjection(Projections.rowCount());
        List<?> list = criteria.list();
        return list.size() > 0 ? (Integer) list.get(0) : 0;
    }

    // -----------------------------------Other -------------------------------
    public void initialize(Object proxy) {
        this.getHibernateTemplate().initialize(proxy);
    }

    public Object merge(Object object) {
        this.getHibernateTemplate().merge(object);
        return object;
    }

    /**
     * hql方式查询，根据属性查询
     * 
     * @param propertyName 属性名称,须同实体的属性名称一致
     * @param value 属性值
     * @author 朱发强
     * @return T类型对象
     */
    public T findOneByProperty(String propertyName, Object value) {
        String queryString = "from " + getClassName() + " as " + getClassAlias() + " where " + getClassAlias() + "."
                             + propertyName + "= ? ";
        List<T> list = this.find(queryString, value);
        T t = null;
        if (list.size() > 0) {
            t = list.get(0);
        }
        return t;
    }

    /**
     * hql方式查询，根据多个属性，属性范围查询数据，
     * 
     * @param propertyNames 属性数组
     * @param values1 起始值数组，如果不是范围，则填目标值
     * @param values2 结束值数组，如果不是范围，则传空字符串
     * @param firstResult 开始记录的序列号
     * @param maxResult 需要取得的记录数
     * @return list
     */
    public List<T> findByProperty(String[] propertyNames, Object[] values1, Object[] values2, int firstResult,
                                  int maxResult) {
        List<String> vtValues = new ArrayList<String>();
        Object[] values = null;
        String queryString = "from " + getClassName() + " as " + getClassAlias() + " where";
        for (int i = 0; i < propertyNames.length; i++) {
            if (null == values2[i] && "".equals(values2[i].toString())) {
                queryString += " " + getClassAlias() + "." + propertyNames[i] + "=? and";
                vtValues.add(values1[i].toString());
            } else {
                queryString += " " + getClassAlias() + "." + propertyNames[i] + "between ? and ? and";
                vtValues.add(values1[i].toString());
                vtValues.add(values2[i].toString());
            }
        }
        values = new Object[vtValues.size()];
        values = vtValues.toArray(values);
        queryString = StringUtils.removeEnd(queryString, "and");
        // queryString=addOrderBy(queryString);
        return this.find(queryString, values, firstResult, maxResult);
    }

    public Page findPage(final DetachedCriteria dc, final PageQueryParameter pageParameter) {
        final Page page = new Page(pageParameter);
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                List<?> list = dc.getExecutableCriteria(session).setFirstResult(pageParameter.getFirstIndex()).setMaxResults(pageParameter.getPageSize()).list();
                page.setRecords(list);
                Integer total = (Integer) dc.getExecutableCriteria(session).setFirstResult(0).setMaxResults(1).setProjection(Projections.rowCount()).uniqueResult();
                page.setTotal(total == null ? 0 : total);
                return page;
            }
        });
        return page;
    }

    public Page findPage(final String hql, final Object[] parameters, final PageQueryParameter pageParameter) {
        final Page page = new Page(pageParameter);
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = createQuery(hql, parameters, session);
                List<?> list = query.setFirstResult(pageParameter.getFirstIndex()).setMaxResults(pageParameter.getPageSize()).list();
                page.setRecords(list);
                String countHql = createCountQL(hql);
                Long total = (Long) createQuery(countHql, parameters, session).uniqueResult();
                page.setTotal(total.intValue());
                return page;
            }
        });
        return page;
    }

    private Query createQuery(final String hql, final Object[] parameters, Session session) {
        Query query = session.createQuery(hql);
        for (int i = 0; parameters != null && i < parameters.length; i++) {
            query.setParameter(i, parameters[i]);
        }
        return query;
    }

    private SQLQuery createSqlQuery(final String sql, final Object[] parameters, Session session) {
        SQLQuery query = session.createSQLQuery(sql);
        for (int i = 0; parameters != null && i < parameters.length; i++) {
            query.setParameter(i, parameters[i]);
        }
        return query;
    }

    protected String createCountQL(String ql) {
        // TODO 处理DISTINCT
        Pattern pattern = Pattern.compile("^\\s*select.+?from", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(ql);
        if (matcher.find()) {
            ql = matcher.replaceFirst("select count(*) from");
        } else if (ql.trim().toLowerCase().startsWith("from")) {
            ql = "select count(*) " + ql;
        } else {
            throw new IllegalArgumentException("is not start with 'select...from' or 'from'");
        }
        return ql;
    }

    public List<?> getResultBySql(final String sql) {
        return (List<?>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                return session.createSQLQuery(sql).list();
            }
        });
    }

    public List<T> findBySql(final String sql) {
        // ??跟getResultBySql有何区别
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                return session.createSQLQuery(sql).addEntity(entityClass).list();
            }
        });
    }

   /* public List queryBySql(final String sql) {
        // 执行sql，不和对象绑定
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                return session.createSQLQuery(sql).list();
            }
        });
    }*/

    public void flush() {
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                session.flush();
                return null;
            }
        });
    }

    public List<T> findBySql(final String sql, final Object[] parameters, final int firstIndex, final int maxResults) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(sql).addEntity("entity", entityClass);
                for (int i = 0; parameters != null && i < parameters.length; i++) {
                    Object param = parameters[i];
                    query.setParameter(i, param);
                }
                return query.setFirstResult(firstIndex).setMaxResults(maxResults).list();
            }
        });
    }

    public Page findPageBySql(final String sql, final Object[] parameters, final PageQueryParameter pageParameter) {
        final Page page = new Page(pageParameter);
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                List<T> list = findBySql(sql, parameters, page.getFirstIndex(), page.getPageSize());
                page.setRecords(list);
                page.setTotal(getRowCountBySql(sql, parameters));
                return page;
            }
        });
        return page;
    }

    public int getRowCountBySql(final String sql, final Object[] parameters) {
        Number total = (Number) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String countQL = createCountQL(sql);
                Number total = (Number) createSqlQuery(countQL, parameters, session).uniqueResult();
                return total;
            }
        });
        return total.intValue();
    }

    public <T1> T1 findById(Class<T1> clazz, Serializable id) {
        return (T1) getHibernateTemplate().get(clazz, id);
    }

    public Page findBySearchModels(final List<SearchModel> searchModels, PageQueryParameter param)
            throws IllegalArgumentException, SecurityException, IllegalAccessException, InvocationTargetException,
            NoSuchMethodException {
        final Page page = new Page(param);
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Criteria criteria = session.createCriteria(entityClass);
                // 解析List<SearchModel>
                /*ComparatorForSearchModel comparator = new ComparatorForSearchModel();
                Collections.sort(searchModels, comparator);*/
                for (SearchModel searchModel : searchModels) {
                    if (!searchModel.getLevel().equals(0)) {
                        criteria = criteria.createCriteria(searchModel.getEntityProperty());
                    }
                    for (SearchAttribute searchattribute : searchModel.getSearchAttributes()) {
                        try {
                            criteria.add(HibernateHelper.invokeRestritionMethod(searchattribute.getConstraint(),
                                                                                searchattribute.getPropertyName(),
                                                                                searchattribute.getValue()));
                        } catch (Exception e) {
                            logger.error(e.getMessage(), e);
                        }
                    }
                }
                criteria.setFirstResult(page.getFirstIndex());
                criteria.setMaxResults(page.getPageSize());
                List<?> list = criteria.list();
                // 计算总记录数
                criteria.setFirstResult(0);
                criteria.setMaxResults(page.getPageSize());
                Integer total = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
                criteria.setProjection(null);
                page.setTotal(total == null ? 0 : total);
                page.setRecords(list);
                return page;
            }
        });
        return page;
    }

    public Page findBySearchModels(final List<SearchModel> searchModels, PageQueryParameter param, final Order order)
            throws IllegalArgumentException, SecurityException, IllegalAccessException, InvocationTargetException,
            NoSuchMethodException {
        final Page page = new Page(param);
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Criteria criteria = session.createCriteria(entityClass);
                // 解析List<SearchModel>
                /*ComparatorForSearchModel comparator = new ComparatorForSearchModel();
                Collections.sort(searchModels, comparator);*/
                for (SearchModel searchModel : searchModels) {
                    if (!searchModel.getLevel().equals(0)) {
                        criteria = criteria.createCriteria(searchModel.getEntityProperty());
                    }
                    for (SearchAttribute searchattribute : searchModel.getSearchAttributes()) {
                        try {
                            criteria.add(HibernateHelper.invokeRestritionMethod(searchattribute.getConstraint(),
                                                                                searchattribute.getPropertyName(),
                                                                                searchattribute.getValue()));
                        } catch (Exception e) {
                            logger.error(e.getMessage(), e);
                        }
                    }
                }
                criteria.addOrder(order);
                criteria.setFirstResult(page.getFirstIndex());
                criteria.setMaxResults(page.getPageSize());
                List<?> list = criteria.list();
                // 计算总记录数
                criteria.setFirstResult(0);
                criteria.setMaxResults(page.getPageSize());
                Integer total = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
                criteria.setProjection(null);
                page.setTotal(total == null ? 0 : total);
                page.setRecords(list);
                return page;
            }
        });
        return page;
    }

    public List<?> findByCriteriaForAnyTypeObject(DetachedCriteria criteria, int firstResult, int maxResults) {
        return getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults);
    }

    public Object persist(Object obj) {
        getHibernateTemplate().persist(obj);
        return obj;
    }

    public void setFlushMode(final FlushMode flushMode) {
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                session.setFlushMode(flushMode);
                return null;
            }
        });
    }

    public boolean contains(Object obj) {
        return getHibernateTemplate().contains(obj);
    }

    public void refresh(Object obj) {
        getHibernateTemplate().refresh(obj);
    }

    public void clear() {
        getHibernateTemplate().clear();
    }
}
